﻿/*
 * Created by SharpDevelop.
 * User: Administrator
 * Date: 2011-1-12
 * Time: 14:03
 * 
 */
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Description of SqlHelper.Instance.
/// </summary>
public class SqlHelper
{
    public static readonly SqlHelper Instance = new SqlHelper(ConfigurationManager.ConnectionStrings["defCon"].ConnectionString);

    // Fields
    private string connectionString;
    private CommandType defaultCommandType;

    // Methods
    public SqlHelper(string connectionstring) :
        this(connectionstring, CommandType.Text)
    {
    }

    public SqlHelper(string connectionstring, CommandType cmdtype)
    {
        this.connectionString = connectionstring;
        this.defaultCommandType = cmdtype;
    }

    private SqlConnection CreateConnection()
    {
        return new SqlConnection(this.connectionString);
    }

    public DataSet ExecuteDataset(string cmdText, params IDataParameter[] cmdParms)
    {
        return this.ExecuteDataset(CommandType.Text, cmdText, cmdParms);
    }

    public DataSet ExecuteDataset(CommandType cmdType, string cmdText, params IDataParameter[] cmdParms)
    {
        using (SqlConnection connection = CreateConnection())
        {
            DataSet dataSet = new DataSet();
            SqlCommand cmd = new SqlCommand();
            this.PrepareCommand(cmd, connection, cmdType, cmdText, cmdParms);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                da.Fill(dataSet);
            return dataSet;
        }
    }

    public int ExecuteNonQuery(string cmdText, params IDataParameter[] cmdParms)
    {
        return this.ExecuteNonQuery(CommandType.Text, cmdText, cmdParms);
    }

    public int ExecuteNonQuery(CommandType cmdType, string cmdText, params IDataParameter[] cmdParms)
    {
        SqlCommand cmd = new SqlCommand();
        using (SqlConnection connection = CreateConnection())
        {
            this.PrepareCommand(cmd, connection, cmdType, cmdText, cmdParms);
            int num = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return num;
        }
    }

    public IDataReader ExecuteReader(string cmdText, params IDataParameter[] cmdParms)
    {
        return this.ExecuteReader(CommandType.Text, cmdText, cmdParms);
    }

    public IDataReader ExecuteReader(CommandType cmdType, string cmdText, params IDataParameter[] cmdParms)
    {
        IDataReader reader2;
        SqlCommand cmd = new SqlCommand();
        SqlConnection conn = CreateConnection();
        try
        {
            this.PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms);
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            reader2 = reader;
        }
        catch
        {
            conn.Close();
            throw;
        }
        return reader2;
    }

    public object ExecuteScalar(string cmdText, params IDataParameter[] cmdParms)
    {
        return this.ExecuteScalar(CommandType.Text, cmdText, cmdParms);
    }

    public object ExecuteScalar(CommandType cmdType, string cmdText, params IDataParameter[] cmdParms)
    {
        SqlCommand cmd = new SqlCommand();
        using (SqlConnection connection = CreateConnection())
        {
            this.PrepareCommand(cmd, connection, cmdType, cmdText, cmdParms);
            object obj2 = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            if (obj2 == DBNull.Value)
            {
                obj2 = null;
            }
            return obj2;
        }
    }

    private void PrepareCommand(SqlCommand cmd, SqlConnection conn, CommandType cmdType, string cmdText, IDataParameter[] cmdParms)
    {
        if (conn.State != ConnectionState.Open)
        {
            conn.Open();
        }
        cmd.Connection = conn;
        cmd.CommandText = cmdText;
        cmd.CommandType = cmdType;
        if (cmdParms != null)
        {
            foreach (SqlParameter parameter in cmdParms)
            {
                if (parameter != null)
                {
                    if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
    }

    // Properties
    public string ConnectionString
    {
        get
        {
            return this.connectionString;
        }
        set
        {
            this.connectionString = value;
        }
    }

    public CommandType DefaultCommandType
    {
        get
        {
            return this.defaultCommandType;
        }
        set
        {
            this.defaultCommandType = value;
        }
    }
}